package com.siemens.industrial.web.mvc.dao.admin;

import com.siemens.industrial.model.admin.Service;
import com.siemens.industrial.model.admin.Tenant;
import com.siemens.industrial.web.mvc.pojo.TenantServiceItem;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.annotation.Resource;
import java.sql.Types;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/9/18.
 */
@org.springframework.stereotype.Service
public class ServiceDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String getServiceListQuery = "SELECT * FROM `siemens`.`service`;";
    private static final String updateQuery="UPDATE `siemens`.`service` SET `service_name`=?, `service_desc`=?, `enabled`=? WHERE `service_id`=?;";
    private static final String getOneByIdQuery = "SELECT * FROM `siemens`.`service` WHERE `service_id`=?";
    private static final String addServiceQuery = "INSERT INTO `siemens`.`service` (`service_id`, `service_name`, `service_desc`, `enabled`) VALUES (?, ?, ?, ?);";
    private static final String updateServiceEnableQuery = "UPDATE `siemens`.`service` SET `enabled`=? WHERE `service_id`=?";
    private static final String tenantServiceManageQuery = "SELECT \n" +
            "    a.tenant_id,\n" +
            "    GROUP_CONCAT(a.service_id) serviceIds,\n" +
            "    GROUP_CONCAT(CONCAT(a.start_date, ' - ', a.end_date)) valid,\n" +
            "    GROUP_CONCAT(a.enabled) enabled,\n" +
            "    b.tenant_name,\n" +
            "    GROUP_CONCAT(c.service_name) names,\n" +
            "    ifnull(d.count,0) count\n" +
            "FROM\n" +
            "    siemens.tenant_service_relation a\n" +
            "        INNER JOIN\n" +
            "    siemens.tenant b ON a.tenant_id = b.tenant_id\n" +
            "        INNER JOIN\n" +
            "    siemens.service c ON a.service_id = c.service_id\n" +
            "        LEFT JOIN \n" +
            "    (SELECT \n" +
            "        tenant_id, COUNT(1) `count`\n" +
            "    FROM\n" +
            "        siemens.device\n" +
            "    GROUP BY tenant_id) d ON a.tenant_id = d.tenant_id\n" +
            "GROUP BY a.tenant_id;";
    private static final String serverStatisticsQuery = "select a.service_id AS serviceId,a.service_name AS name,count(d.device_id) AS `value` from `siemens`.`service` a left join `siemens`.`tenant_service_relation` b on a.service_id=b.service_id left join `siemens`.`tenant` c on b.tenant_id=c.tenant_id left join `siemens`.`device` d on b.tenant_id=d.tenant_id group by serviceId;";

    private static final String deviceCountOfTenant = "select c.tenant_name,b.count from (SELECT a.tenant_id,count(a.device_id) 'count' FROM siemens.device a where a.delete_time is null group by a.tenant_id) b inner join siemens.tenant c on b.tenant_id = c.tenant_id limit 5;";

    /**
     * 服务项目列表
     *
     * @return
     */
    public List<Service> getServices() {
        return jdbcTemplate.query(getServiceListQuery, Service.getRowMapper());
    }

    public Service findOneById(String serviceId) {
        try {
            return jdbcTemplate.queryForObject(getOneByIdQuery, new Object[]{serviceId}, new int[]{Types.VARCHAR},Service.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public int update(Service service) {
        return jdbcTemplate.update(updateQuery, pss -> {
            pss.setString(1, service.getService_name());
            pss.setString(2, service.getService_desc());
            pss.setInt(3, service.getEnabled());
            pss.setString(4, service.getService_id());
        });
    }

    public void save(Service service) {
        jdbcTemplate.update(addServiceQuery, pss -> {
            pss.setString(1, service.getService_id());
            pss.setString(2, service.getService_name());
            pss.setString(3, service.getService_desc());
            pss.setInt(4, service.getEnabled());
        });
    }

    /**
     * 租户服务管理
     */
    public List<TenantServiceItem> getTenantServiceList() {
        return jdbcTemplate.query(tenantServiceManageQuery, TenantServiceItem.getRowMapper());
    }

    /**
     * 禁用 启用 服务项目
     *
     * @param serviceId
     * @param enabled
     * @return
     */
    public int updateEnabled(String serviceId, int enabled) {
        return jdbcTemplate.update(updateServiceEnableQuery, enabled, serviceId);
    }

    /**
     * 各服务订阅量统计
     *
     * @return
     */
    public List<Map<String, Object>> getServerStatistics() {

        return jdbcTemplate.queryForList(serverStatisticsQuery);
    }

    public List<Map<String, Object>> getDeviceCountOfTenant() {
        return jdbcTemplate.queryForList(deviceCountOfTenant);
    }
}
